USE [HR_DB]
GO
if exists(select * from dbo.sysobjects where id = object_id(N'human_file')   and   OBJECTPROPERTY(id,   N'IsUserTable')   =   1)   
  drop table human_file   
-- =============================================
-- Author:杨洁(kaixuanpisces)
-- Create date: 20090728
-- Description:人力资源档案
-- =============================================
CREATE TABLE [dbo].[human_file](
	[huf_id] [int] IDENTITY(1,1) NOT NULL,
	[human_id] [varchar](30) NULL,
	[first_kind_id] [char](2) NULL,
	[first_kind_name] [varchar](60) NULL,
	[second_kind_id] [char](2) NULL,
	[second_kind_name] [varchar](60) NULL,
	[third_kind_id] [char](2) NULL,
	[third_kind_name] [varchar](60) NULL,
	[human_name] [varchar](60) NULL,
	[human_address] [varchar](200) NULL,
	[human_postcode] [varchar](10) NULL,
	[human_pro_designation] [varchar](60) NULL,
	[human_major_kind_id] [char](2) NULL,
	[human_major_kind_name] [varchar](60) NULL,
	[human_major_id] [char](2) NULL,
	[human_major_name] [varchar](60) NULL,
	[human_telephone] [varchar](20) NULL,
	[human_mobilephone] [char](11) NULL,
	[human_bank] [varchar](50) NULL,
	[human_account] [varchar](30) NULL,
	[human_qq] [varchar](15) NULL,
	[human_email] [varchar](50) NULL,
	[human_hobby] [varchar](60) NULL,
	[human_speciality] [varchar](60) NULL,
	[human_sex] [char](2) NULL,
	[human_religion] [varchar](50) NULL,
	[human_party] [varchar](50) NULL,
	[human_nationality] [varchar](50) NULL,
	[human_race] [varchar](50) NULL,
	[human_birthday] [datetime] NULL,
	[human_birthplace] [varchar](50) NULL,
	[human_age] [smallint] NULL,
	[human_educated_degree] [varchar](60) NULL,
	[human_educated_years] [smallint] NULL,
	[human_educated_major] [varchar](60) NULL,
	[human_society_security_id] [varchar](30) NULL,
	[human_id_card] [varchar](20) NOT NULL,
	[remark] [text] NULL,
	[salary_standard_id] [varchar](30) NULL,
	[salary_standard_name] [varchar](60) NULL,
	[salary_sum] [money] NULL,
	[demand_salaray_sum] [money] NULL,
	[paid_salary_sum] [money] NULL,
	[major_change_amount] [smallint] NULL,
	[bonus_amount] [smallint] NULL,
	[training_amount] [smallint] NULL,
	[file_chang_amount] [smallint] NULL,
	[human_histroy_records] [text] NULL,
	[human_family_membership] [text] NULL,
	[human_picture] [varchar](255) NULL,
	[attachment_name] [varchar](255) NULL,
	[check_status] [smallint] NULL,
	[register] [smallint] NULL,
	[checker] [smallint] NULL,
	[changer] [smallint] NULL,
	[regist_time] [datetime] NULL,
	[check_time] [datetime] NULL,
	[change_time] [datetime] NULL,
	[lastly_change_time] [datetime] NULL,
	[delete_time] [datetime] NULL,
	[recovery_time] [datetime] NULL,
	[human_file_status] [bit] NULL,
 CONSTRAINT [human_file_PK] PRIMARY KEY CLUSTERED 
(
	[huf_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

-- ==============================================
-- 列名说明
-- ==============================================
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键,自动增长列' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'huf_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'档案编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'一级机构编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'first_kind_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'一级机构名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'first_kind_name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'二级机构编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'second_kind_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'二级机构名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'second_kind_name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'三级机构编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'third_kind_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'三级机构名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'third_kind_name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_address'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'邮政编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_postcode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'职称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_pro_designation'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'职位分类编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_major_kind_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'职位分类名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_major_kind_name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'职位编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_major_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'职位名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'hunma_major_name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'电话' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_telephone'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'手机号码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_mobilephone'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'开户银行' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_bank'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'银行帐号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_account'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'QQ号码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_qq'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'电子邮件' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_email'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'爱好' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_hobby'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'特长' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_speciality'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_sex'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'宗教信仰' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_religion'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'政治面貌' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_party'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'国籍' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_nationality'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'民族' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_race'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'出生日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_birthday'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'出生地' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_birthplace'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'年龄' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_age'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学历' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_educated_degree'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'教育年限' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_educated_years'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学历专业' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_educated_major'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'社会保障号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_society_security_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'身份证号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_id_card'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'remark'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'薪酬标准编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'salary_standard_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'薪酬标准名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'salary_standard_name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'基本薪酬总额' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'salary_sum'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'应发薪酬总额' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'demand_salaray_sum'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实发薪酬总额' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'paid_salary_sum'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'调动次数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'major_change_amount'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'奖励累计次数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'bonus_amount'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'培训累计次数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'training_amount'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'档案变更累计次数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'file_chang_amount'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'简历' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_histroy_records'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'家庭关系' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_family_membership'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'相片' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_picture'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'附件名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'attachment_name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'复核状态' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'check_status'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'档案登记人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'register'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'档案复核人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'checker'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'档案变更人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'changer'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'档案登记时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'regist_time'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'档案复核时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'check_time'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'档案变更时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'change_time'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'档案最近更改时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'lastly_change_time'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'档案删除时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'delete_time'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'档案恢复时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'recovery_time'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'档案状态' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'human_file', @level2type=N'COLUMN',@level2name=N'human_file_status'

-- ===============================================
-- 测试数据
-- ===============================================
INSERT INTO human_file 
		   (human_id,first_kind_id,first_kind_name,second_kind_id,second_kind_name,third_kind_id,third_kind_name ,human_name,human_address,human_postcode,human_pro_designation
           ,human_major_kind_id,human_major_kind_name,human_major_id,hunma_major_name,human_mobilephone,human_bank,human_account,human_qq,human_email,human_hobby,human_speciality
           ,human_sex,human_religion,human_party,human_nationality,human_race,human_birthday,human_birthplace,human_age,human_educated_degree,human_educated_years,human_educated_major
           ,human_id_card,check_status,register,regist_time,human_file_status)
     VALUES
		   ('bt0101010001','01','harry集团','01','harry软件公司','01','外包组','fantia','北京海淀','100091','经理'
		   ,'02','软件开发','01','项目经理','13699175041','建设银行','123456879586584','26284795','26284795@qq.com','舞蹈','java'
		   ,'女','无','党员','中国','汉族','1983-07-01','太原',24,'本科','16','生物工程'
		   ,'140105198307010065',0,1,'2007-05-31',1)

